package com.exer.dataload.DorisDDLConverter.impl;

import com.exer.dataload.DorisDDLConverter.DorisDDLConverter;
import com.exer.dataload.config.DriverConfig;
import com.exer.dataload.utils.Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 将mysql的DDL语句转换成doris的DDL语句
 */
public class DorisDDLConverterFromMySQL implements DorisDDLConverter {


    /**
     * mysql类型到doris类型的映射
     */
    private static final Map<String, String> typeMap = new LinkedHashMap<>();


    private String mysqlDDL;
    private String mysqlTableName;

    static {
        /**
         * 初始化类型映射
         */
        typeMap.put("BOOLEAN", "TINYINT");
        typeMap.put("TINYINT", "TINYINT");
        typeMap.put("SMALLINT", "SMALLINT");
        typeMap.put("MEDIUMINT", "INT");
        typeMap.put("INT", "INT");
        typeMap.put("BIGINT", "BIGINT");
        typeMap.put("UNSIGNED TINYINT", "SMALLINT");
        typeMap.put("UNSIGNED MEDIUMINT", "INT");
        typeMap.put("UNSIGNED INT", "BIGINT");
        typeMap.put("UNSIGNED BIGINT", "LARGEINT");
        typeMap.put("FLOAT", "FLOAT");
        typeMap.put("DOUBLE", "DOUBLE");
        typeMap.put("DECIMAL", "DECIMAL");
        typeMap.put("UNSIGNED DECIMAL", "STRING");
        typeMap.put("DATE", "DATEV2");
        typeMap.put("TIMESTAMP", "DATETIME");
        typeMap.put("DATETIME", "DATETIME");
        typeMap.put("YEAR", "SMALLINT");
        typeMap.put("TIME", "STRING");
        typeMap.put("CHAR", "CHAR");
        typeMap.put("VARCHAR", "VARCHAR");
        typeMap.put("JSON", "STRING");
//        typeMap.put("SET","STRING");
//        typeMap.put("ENUM","STRING");
        typeMap.put("BIT", "STRING");
        typeMap.put("TINYTEXT", "STRING");
        typeMap.put("TEXT", "STRING");
        typeMap.put("MEDIUMTEXT", "STRING");
        typeMap.put("LONGTEXT", "STRING");
        typeMap.put("BLOB", "STRING");
        typeMap.put("MEDIUMBLOB", "STRING");
        typeMap.put("LONGBLOB", "STRING");
        typeMap.put("TINYBLOB", "STRING");
        typeMap.put("BINARY", "STRING");
        typeMap.put("VARBINARY", "STRING");
        typeMap.put("OTHER", "UNSUPPORTED");
    }

    public DorisDDLConverterFromMySQL(String mysqlDDL, String mysqlTableName) {
        this.mysqlDDL = mysqlDDL;
        this.mysqlTableName = mysqlTableName;
    }

    public DorisDDLConverterFromMySQL(String mysqlTableName) {
        this.mysqlTableName = mysqlTableName;
    }


    public void setMysqlDDL(String mysqlDDL) {
        this.mysqlDDL = mysqlDDL;
    }

    public void setMysqlTableName(String mysqlTableName) {
        this.mysqlTableName = mysqlTableName;
    }

    /**
     * 获取doris的DDL语句,
     * 若属性mysqlDDL为空,会根据属性mysqlTableName中去查询DDL语句
     *
     * @return doris的DDL语句
     */
    @Override
    public String getDorisDDL() {
        //校验
        if (mysqlDDL == null && mysqlTableName != null) {
            getMysqlDDLByTableName(mysqlTableName);
        }
        if (mysqlDDL == null || "".equals(mysqlDDL)) {
            throw new RuntimeException("mysqlDDL语句错误");
        }
        //获取表名
        String tableName = Utils.findSub(mysqlDDL, "(?i)\\b(CREATE|ALTER)\\s+TABLE\\s+(?:`([^`]+)`|\\w+)", 2);
        if (tableName == null) {
            throw new RuntimeException("mysqlDDL中未找到表名");
        }
        //找表注释
        String collate = Utils.findSub(mysqlDDL, "(?i)COMMENT='(.*?)'", 1);
        //找主键
        String createTablePrimaryKey = Utils.findSub(mysqlDDL, "(?i)PRIMARY\\s+KEY\\s*\\(([^\\(\\)]*)\\)", 1);
        String alterTablePrimaryKey = Utils.findSub(mysqlDDL, "(?i)ALTER\\s+TABLE\\s+(\\w+)\\s+ADD\\s+PRIMARY\\s+KEY\\s*\\(\\s*(\\w+)", 2);
        if (createTablePrimaryKey == null && alterTablePrimaryKey == null) {
            throw new RuntimeException("mysqlDDL中未找到主键");
        }
        String primaryKey = createTablePrimaryKey != null ? createTablePrimaryKey : alterTablePrimaryKey;
        //替换掉doris不支持的语句
        String ddlStatement = mysqlDDL.replaceAll("(?i)\\s+AUTO_INCREMENT", " ")
                .replaceAll("(?i)\\s+COLLATE\\s+[^\\s(,]+", " ")
                .replaceAll("(?i)\\s+CHARACTER\\s+SET\\s+[^\\s(,]+", " ")
                .replaceAll("(?i)\\s+DEFAULT\\s+current_timestamp\\(\\)", " DEFAULT NULL ")
                .replaceAll("(?i)\\s+NOT\\s+NULL\\s+DEFAULT\\s+CURRENT_TIMESTAMP", " NULL DEFAULT NULL ")
                .replaceAll("(?i)\\s+DEFAULT\\s+CURRENT_TIMESTAMP", " DEFAULT NULL ")
                .replaceAll("(?i)\\s+ON\\s+UPDATE\\s+current_timestamp\\(\\)", " ")
                .replaceAll("(?i)\\s+ON\\s+UPDATE\\s+CURRENT_TIMESTAMP", " ")
                .replaceAll("(?i)\\s+DEFAULT\\s+b", " DEFAULT ")
                .replaceAll("(?i)\\s+enum\\((?:[^,(\\)]+[, ]?)+\\)", " STRING ")
                .replaceAll("\\b(PRIMARY\\s+KEY|UNIQUE\\s+KEY|UNIQUE\\s+INDEX|KEY|INDEX|FULLTEXT)\\b[^,].*", "")
                //删除最后一个括号前的逗号
                .replaceFirst(",(?=\\s*\\))", "")
                //删除最后一个括号后所有内容
                .replaceFirst("\\)[^()]*(?=\\s*$)", ")");
        //varchar类型长度剩3
        ddlStatement = expandVarcharLengthsInDDL(ddlStatement, "varchar\\((\\d+)\\)");
        //替换掉doris不支持的类型
        ddlStatement = replaceKeywords(ddlStatement);
        //构建最终dorisDDL语句
        StringBuilder dorisDDl = new StringBuilder(ddlStatement);
        dorisDDl.append("\nUNIQUE KEY(").append(primaryKey).append(")\n");
        if (collate != null) {
            dorisDDl.append("COMMENT '").append(collate).append("'\n");
        }
        dorisDDl.append("DISTRIBUTED BY HASH(").append(primaryKey).append(") BUCKETS 1\n");
        dorisDDl.append("PROPERTIES\n" +
                "(\n" +
                "    \"replication_num\" = \"1\"\n" +
                ");");
        return dorisDDl.toString();
    }

    /**
     * varchar类型长度乘3
     *
     * @param mysqlDDL
     * @param regex
     * @return
     */
    private String expandVarcharLengthsInDDL(String mysqlDDL, String regex) {
        Pattern pattern = Pattern.compile(regex);
        StringBuffer modifiedSql = new StringBuffer();
        Matcher matcher = pattern.matcher(mysqlDDL);
        while (matcher.find()) {
            int originalLength = Integer.parseInt(matcher.group(1));
            int newLength = originalLength * 3;
            String replacement = "varchar(" + newLength + ")";
            // 替换匹配项
            matcher.appendReplacement(modifiedSql, replacement);
        }
        matcher.appendTail(modifiedSql);
        return modifiedSql.toString();
    }

    /**
     * 将mysql的类型替换为doris的类型
     *
     * @param mysqlDDL 原始字符串
     * @return 替换后的字符串
     */
    private String replaceKeywords(String mysqlDDL) {
        for (String mysqlType : typeMap.keySet()) {
            if (Pattern.compile("(?i)\\b(" + mysqlType + ")\\b").matcher(mysqlDDL).find()) {
                if (typeMap.get(mysqlType).equalsIgnoreCase("STRING")) {
                    mysqlDDL = mysqlDDL.replaceAll("(?i)\\s+(" + mysqlType + ")(\\s*\\([^)]*\\)|\\s*)", " STRING ");
                } else {
                    mysqlDDL = mysqlDDL.replaceAll("(?i)\\b(" + mysqlType + ")\\b", typeMap.get(mysqlType));
                }
            }
        }
        return mysqlDDL;
    }

    /**
     * 根据表名去mysql中查询DDL语句
     *
     * @param mysqlTableName mysql表名
     */
    private void getMysqlDDLByTableName(String mysqlTableName) {
        if (mysqlDDL == null && mysqlTableName != null) {
            try (Connection mysqlConn = DriverConfig.getMysqlConnection();
                 PreparedStatement statement = mysqlConn.prepareStatement("SHOW CREATE TABLE " + mysqlTableName);
                 ResultSet resultSet = statement.executeQuery()) {
                if (!resultSet.next()) {
                    throw new RuntimeException("Table '" + mysqlTableName + "' doesn't exist");
                }
                String string = resultSet.getString(2);
                setMysqlDDL(string);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
